﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_PGM_GRID_COLUMN_Insert')
	BEGIN
		DROP Procedure usp_UPDMS_COM_PGM_GRID_COLUMN_Insert
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_PGM_GRID_COLUMN_Insert
**	Desc : 프로그램별 그리드별 컬럼을 Insert한다.
**	Test Exec Query : 아래 참고
**	Called by : Com_Dac_UPDMS_COM_PGM_GRID_COLUMN.cs
**	Program ID : Com1019m
**	Auth : 송시명
**	Date : 2011-02-16
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_PGM_GRID_COLUMN_Insert]
@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드
AS

BEGIN

DECLARE @iXmlHandle int
DECLARE @sPgmId nvarchar(20)
DECLARE @sGridId nvarchar(50)

EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml

SET @sPgmId = ( SELECT TOP 1 Pgm_Id FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_PGM_GRID_COLUMN', 2) WITH ( Pgm_Id nvarchar(20)) )
SET @sGridId = ( SELECT TOP 1 Grid_Id FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_PGM_GRID_COLUMN', 2) WITH ( Grid_Id nvarchar(50)) )

--프로그램아이디,그리드아이디 모두삭제
DELETE FROM UPDMS_COM_PGM_GRID_COLUMN 
 WHERE Pgm_Id = @sPgmId
   AND Grid_Id = @sGridId


--다시 저장
INSERT INTO UPDMS_COM_PGM_GRID_COLUMN (	
       Pgm_Id,
       Grid_Id,
       Col_Idx,
       Label_Id,
       Col_Nm,
       Create_User,
       Create_Dt,
       Update_User,
       Update_Dt
)
SELECT Pgm_Id,
       Grid_Id,
       Col_Idx,
       Label_Id,
       Col_Nm,
       Create_User,
       GETDATE(),
       Update_User,
       GETDATE()
  FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_PGM_GRID_COLUMN', 2) 
  WITH (
       Pgm_Id nvarchar(20),
       Grid_Id nvarchar(50),
       Col_Idx int,
       Label_Id int,
       Col_Nm varchar(200),
       Create_User nvarchar(20),
       Create_Dt datetime,
       Update_User nvarchar(20),
       Update_Dt datetime
       ) X
 WHERE X.Pgm_Id IS NOT NULL
   AND X.Grid_Id IS NOT NULL
   AND X.Col_Idx IS NOT NULL
   AND X.Label_Id IS NOT NULL

--메모리 해제
EXEC sp_xml_removedocument @iXmlHandle

END

/*
test query
DECLARE @ls_xml nvarchar(max) 
SET @ls_xml=''

EXEC usp_UPDMS_COM_PGM_GRID_COLUMN_Insert @ls_xml
*/